package com.gsma.calendar.android.db;

import java.io.InputStreamReader;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.text.TextUtils;
import android.util.Log;

import com.gsma.calendar.android.db.CalendarGsmaDBSchema.Tables;
import com.gsma.calendar.android.io.CSVReader;
import com.gsma.calendar.android.util.Config;

public class DatabaseController extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "GSMA.calendar.db";
	private static final int DATABASE_VERSION = 17;
	private Context context;

	public DatabaseController(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;

		Log.i(Config.LOG_TAG, "Db version " + DATABASE_VERSION + " is opened");
	}

	public void copyDbFileFromAssets(Context context, SQLiteDatabase db) {

		CSVReader reader = null;
		InputStreamReader in = null;

		try {
			in = new InputStreamReader(context.getAssets().open("Agenda.csv"));
			reader = new CSVReader(in);

			// skip header
			reader.readNext();
			reader.readNext();

			String[] data = null;
			String day = null;
			ContentValues values = null;

			while ((data = reader.readNext()) != null) {
				if (!TextUtils.isEmpty(data[0]) && TextUtils.isEmpty(data[1])) {
					day = data[0];
				} else if (!TextUtils.isEmpty(data[0])) {
					// System.out.println(sf.parse(day + " " +
					// data[0]).getTime());
					// System.out.println(sf.parse(day + " " +
					// data[1]).getTime());
					// System.out.println(data[2]);
					// System.out.println(data[3]);
					// System.out.println(data[4]);
					// System.out.println(data[5]);
					// System.out.println(data[6]);
					// System.out.println(data[7]);

					values = new ContentValues();
					try {
						values.put(CalendarGSMAColumns.START_TIME.getName(), Config.sdf_full.parse(day + " " + data[0]).getTime());
					} catch (Exception e) {
						e.printStackTrace();
					}
					try {
						values.put(CalendarGSMAColumns.END_TIME.getName(), Config.sdf_full.parse(day + " " + data[1]).getTime());
					} catch (Exception e) {
						e.printStackTrace();
					}
					values.put(CalendarGSMAColumns.EVENT.getName(), data[2]);
					values.put(CalendarGSMAColumns.EVENT_TYPE.getName(), data[3]);
					values.put(CalendarGSMAColumns.SESSION.getName(), data[4]);
					values.put(CalendarGSMAColumns.WHERE.getName(), data[5]);
					values.put(CalendarGSMAColumns.SPEAKERS.getName(), data[6]);
					values.put(CalendarGSMAColumns.TAG.getName(), data[7]);

					Log.e("asd", "" + db.insert(Tables.CALENDAR_GSMA, null, values));

				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				reader.close();
				in.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		ContentValues values = new ContentValues();
		values.put(GlobalEventColumns.START_TIME.getName(), "1330327800000");
		values.put(GlobalEventColumns.END_TIME.getName(), "1330610400000");
		values.put(GlobalEventColumns.EVENT.getName(), "MWC 2012");
		values.put(GlobalEventColumns.LOCATION.getName(), "Location");
		values.put(GlobalEventColumns.OVERVIEW.getName(), "Overview");
		db.insert(Tables.GLOBAL_EVENT, null, values);

		values.put(GlobalEventColumns.START_TIME.getName(), "1330327800000");
		values.put(GlobalEventColumns.END_TIME.getName(), "1330387200000");
		values.put(GlobalEventColumns.EVENT.getName(), "Global Mobile Awards");
		values.put(GlobalEventColumns.LOCATION.getName(), "Barcelona, Spain");
		values.put(GlobalEventColumns.OVERVIEW.getName(), "Overview Global Mobile Awards");
		db.insert(Tables.GLOBAL_EVENT, null, values);

		values.put(GlobalEventColumns.START_TIME.getName(), "1338249600000");
		values.put(GlobalEventColumns.END_TIME.getName(), "1338508800000");
		values.put(GlobalEventColumns.EVENT.getName(), "GSMA-mHealth Alliance Mobile Health Summit");
		values.put(GlobalEventColumns.LOCATION.getName(), "Cape Town, South Africa");
		values.put(GlobalEventColumns.OVERVIEW.getName(), "Overview");
		db.insert(Tables.GLOBAL_EVENT, null, values);

		values.put(GlobalEventColumns.START_TIME.getName(), "1375315200000");
		values.put(GlobalEventColumns.END_TIME.getName(), "1380585600000");
		values.put(GlobalEventColumns.EVENT.getName(), "Mobile Asia Expo");
		values.put(GlobalEventColumns.LOCATION.getName(), "Shanghai, China");
		values.put(GlobalEventColumns.OVERVIEW.getName(), "Overview");
		db.insert(Tables.GLOBAL_EVENT, null, values);

		values.put(GlobalEventColumns.START_TIME.getName(), "1350864000000");
		values.put(GlobalEventColumns.END_TIME.getName(), "1351123200000");
		values.put(GlobalEventColumns.EVENT.getName(), "NFC & Mobile Money Summit");
		values.put(GlobalEventColumns.LOCATION.getName(), "Milan, Italy");
		values.put(GlobalEventColumns.OVERVIEW.getName(), "Overview");
		db.insert(Tables.GLOBAL_EVENT, null, values);

	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		StringBuilder sql = new StringBuilder(1024);
		sql.append("CREATE TABLE ").append(Tables.CALENDAR_GSMA).append(" (");
		for (CalendarGSMAColumns column : CalendarGSMAColumns.values()) {
			if (CalendarGSMAColumns._ID.equals(column)) {
				sql.append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT,");
			} else {
				sql.append(column.getName()).append(column.getType().getName());
			}
		}
		sql.append("UNIQUE (").append(CalendarGSMAColumns.SESSION.getName()).append(") ON CONFLICT REPLACE)");
		db.execSQL(sql.toString());

		sql.setLength(0);
		sql.append("CREATE TABLE ").append(Tables.GLOBAL_EVENT).append(" (");
		for (GlobalEventColumns column : GlobalEventColumns.values()) {
			if (GlobalEventColumns._ID.equals(column)) {
				sql.append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT,");
			} else {
				sql.append(column.getName()).append(column.getType().getName());
			}
		}
		sql.append("UNIQUE (").append(GlobalEventColumns.EVENT.getName()).append(") ON CONFLICT REPLACE)");
		db.execSQL(sql.toString());

		sql.setLength(0);
		sql.append("CREATE TABLE ").append(Tables.LOCAL_CALENDAR_EVENTS).append(" (");
		for (LocalCalendarEventsColumns column : LocalCalendarEventsColumns.values()) {
			if (LocalCalendarEventsColumns._ID.equals(column)) {
				sql.append(BaseColumns._ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT,");
			} else {
				sql.append(column.getName()).append(column.getType().getName());
			}
		}
		sql.append("UNIQUE (").append(LocalCalendarEventsColumns.CALENDAR_ID.getName() + "," + LocalCalendarEventsColumns.SESSION_NAME.getName())
				.append(") ON CONFLICT REPLACE)");
		db.execSQL(sql.toString());

		copyDbFileFromAssets(context, db);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.d(DatabaseController.class.getSimpleName(), "Upgrading database from v." + oldVersion + " to v." + newVersion);

		// drop all tables
		final String tables[] = { Tables.CALENDAR_GSMA, Tables.GLOBAL_EVENT, Tables.LOCAL_CALENDAR_EVENTS };
		for (final String table : tables)
			db.execSQL("DROP TABLE IF EXISTS " + table);

		onCreate(db);
	}

}